package skypeUtil;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SkypeUtilFunctionImpl implements SkypeUtilFunction{

	public ResultSet messageWordSearch(String message,Statement statement){
		try {
			ResultSet rs = statement.executeQuery("SELECT author,from_dispname,dialog_partner,datetime(timestamp, \"unixepoch\") as time,sending_status,body_xml " +
					"FROM Messages " +
					"WHERE body_xml LIKE '%"+message+"%'");
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}
	
	public ResultSet messageWordSearchRadius(String message,Statement statement){
		try {
			ResultSet rs = statement.executeQuery("SELECT C.author,C.dialog_partner,C.body_xml,datetime(C.timestamp, \"unixepoch\")," +
					"A.author,A.dialog_partner,A.body_xml,datetime(A.timestamp, \"unixepoch\")," +
					"B.author,B.dialog_partner,B.body_xml,datetime(B.timestamp, \"unixepoch\")  " +
					"FROM Messages as A LEFT JOIN Messages as B ON A.pk_id=B.pk_id-1 LEFT JOIN Messages as C ON A.pk_id=C.pk_id+1 "+
					"WHERE A.body_xml LIKE '% "+message+" %'");
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}
	
	public ResultSet userMessages(String user,Statement statement){
		try {
			ResultSet rs = statement.executeQuery("SELECT author,from_dispname,dialog_partner,datetime(timestamp, \"unixepoch\") as time,sending_status,body_xml " +
					"FROM Messages " +
					"WHERE body_xml NOT LIKE '%</files>%' AND body_xml NOT LIKE '<partlist alt=\"\">%' AND dialog_partner LIKE '"+user+"%'");
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}
	
	public ResultSet messagePatternSearch(String pattern,Statement statement){
		Pattern p = Pattern.compile(pattern);
		Matcher m;
		boolean b;
		try {
			ResultSet rs = statement.executeQuery("SELECT author,from_dispname,dialog_partner,datetime(timestamp, \"unixepoch\") as time,sending_status,body_xml FROM Messages WHERE body_xml NOT LIKE '<partlist alt=\"\">%'");
			int columnCount=rs.getMetaData().getColumnCount();;
			while (rs.next()){
				m = p.matcher(rs.getString("body_xml"));
				b = m.matches();
				if(b==true){
					for (int i = 1; i <= columnCount; i++) {
						//stream.write(rs.getString(i)+" ");
				        System.out.print(rs.getString(i)+" ");
					}
					//stream.write('\n');
					System.out.println();
				}
			}
			return rs;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}
	
	public void printResultSet(ResultSet rs){
		try {
			int columnCount = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				// read the result set
				//System.out.println(rs.getString("current_video_audience")+rs.getInt("duration")*1.0/360);
				for (int i = 1; i <= columnCount; i++) {
			        System.out.print(rs.getString(i)+" ");
				}
				//System.out.println(rs.getString("author")+" "+rs.getString("from_dispname")+" "+rs.getString("dialog_partner")+" "+rs.getString("timestamp")+" "+rs.getString("sending_status")+" "+rs.getString("body_xml"));
				// System.out.println("id = " + rs.getInt("id"));
				System.out.println();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	void commandList(){
		System.out.println("\n\nAvailable commands are:\n\tmessageWordSearch\n\tmessageWordSearchRadius\n\tuserMessages\n\tmessagePatternSearch\n\t" +
				"numberUserMessages\n\tdurationUserCalls\n\tfirstMessage\n\tcontactList\n\tratingDurationCalls\n\tratingConversationMessages\n\texit\n\n");
	}
	public void parseCommand(String str, Statement statement) {
		String[] strs=str.split(" ");
		int k=0;
		//System.out.println(strs[1]);
		try{
			if(strs[0].equals("messageWordSearch")){
				messageWordSearch(strs[1],statement);
				k=1;
			}
			if(strs[0].equals("messageWordSearchRadius")){
				messageWordSearchRadius(strs[1],statement);
				k=1;
			}
			if(strs[0].equals("userMessages")){
				userMessages(strs[1],statement);
				k=1;
			}
			if(strs[0].equals("messagePatternSearch")){
				messagePatternSearch(strs[1],statement);
				k=1;
			}
			if(strs[0].equals("durationUserCalls")){
				durationUserCalls(strs[1],statement);
				k=1;
			}
			if(strs[0].equals("numberUserMessages")){
				numberUserMessages(strs[1],statement);
				k=1;
			}
			if(str.equals("ratingDurationCalls")){
				ratingDurationCalls(statement);
				k=1;
			}
			if(str.equals("ratingConversationMessages")){
				ratingConversationMessages(statement);
				k=1;
			}
			if(str.equals("contactList")){
				contactList(statement);
				k=1;
			}
			if(str.equals("firstMessage")){
				firstMessage(statement);
				k=1;
			}
			if(str.equals("commandList")){
				commandList();
				k=1;
			}
		}catch (ArrayIndexOutOfBoundsException e){
			System.out.println("Wrong command! Usage:command param\nType \"commandList\" for details");
		}
		if(k==0) System.out.println("Wrong command! Usage:command param\nType \"commandList\" for details");

	}
	@Override
	public ResultSet durationUserCalls(String user, Statement statement) {
		try {
			ResultSet rs = statement.executeQuery("SELECT sum(duration) as dur " +
					"FROM Calls " +
					"WHERE current_video_audience LIKE '"+user+"%'");
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	
	}

	@Override
	public ResultSet numberUserMessages(String user, Statement statement) {
			try {
				ResultSet rs = statement.executeQuery("SELECT count(body_xml) " +
						"FROM Messages " +
						"WHERE body_xml NOT LIKE '%</files>%' AND body_xml NOT LIKE '<partlist alt=\"\">%' AND author LIKE '"+user+"%'");
				printResultSet(rs);
				return rs;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.err.println(e);
			}
			return null;
		}

	@Override
	public ResultSet contactList(Statement statement) {
		try {
			ResultSet rs = statement.executeQuery("SELECT skypename,fullname " +
					"FROM Contacts ");
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}

	@Override
	public ResultSet firstMessage(Statement statement) {
		try {
			ResultSet rs = statement.executeQuery("SELECT datetime(min(timestamp),\"unixepoch\") FROM Messages");
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}

	@Override
	public ResultSet ratingDurationCalls(Statement statement) {
		try {
			ResultSet rs = statement.executeQuery("SELECT T.current_video_audience, count(*) as rating " +
					"FROM (SELECT current_video_audience, sum(duration) as dur " +
						"FROM Calls WHERE current_video_audience is not NULL "+
						"GROUP BY current_video_audience) T "+
					"INNER JOIN (SELECT current_video_audience, sum(duration) as dur " +
					"	FROM Calls WHERE current_video_audience is not NULL "+
						"GROUP BY current_video_audience) L ON T.dur<=L.dur "+
					"GROUP BY T.current_video_audience " +
					"ORDER BY 2");
			
			/*ResultSet rs=statement.executeQuery("SELECT current_video_audience, sum(duration) as dur FROM Calls WHERE current_video_audience is not NULL "+
					"GROUP BY current_video_audience");
			*/
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}

	@Override
	public ResultSet ratingConversationMessages(Statement statement) {
		try {
			ResultSet rs = statement.executeQuery("SELECT T.dialog_partner, count(*) as rating " +
					"FROM (SELECT dialog_partner,count(*) as amount FROM Messages WHERE dialog_partner is not NULL " +
						"GROUP BY dialog_partner) T " +
					"INNER JOIN  (SELECT dialog_partner,count(*) as amount FROM Messages WHERE dialog_partner is not NULL " +
						"GROUP BY dialog_partner) L ON T.amount<=L.amount "+
					"GROUP BY T.dialog_partner ORDER BY 2");
			
			/*ResultSet rs=statement.executeQuery("SELECT current_video_audience, sum(duration) as dur FROM Calls WHERE current_video_audience is not NULL "+
					"GROUP BY current_video_audience");
			*/
			printResultSet(rs);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println(e);
		}
		return null;
	}



}